from connsqlite.connect_sqlite3 import Sqlite3Database

"""
 bin与银行关联信息表
"""


class RapidCrawlerBinManager:
    def __init__(self):
        self.db_name = "rapid.db"
        self.table_name = "rapid_crawler_bin_info"
        self.columns = "id INTEGER PRIMARY KEY, " \
                       "organization TEXT(200), " \
                       "bin_number TEXT(200), " \
                       "country TEXT(100), " \
                       "bank TEXT(100), " \
                       "card TEXT(100), " \
                       "card_type TEXT(100), " \
                       "card_level TEXT(100), " \
                       "remark TEXT(2000), " \
                       "create_time TEXT(100) DEFAULT CURRENT_TIMESTAMP, " \
                       "update_time TEXT(100) DEFAULT CURRENT_TIMESTAMP, " \
                       "CONSTRAINT unique_crawler_bin UNIQUE (bin_number,country,bank)"
        self.sqlite3 = Sqlite3Database(self.db_name)
        self.sqlite3.create_table(self.table_name, self.columns)

    def selectDataByPager(self, offset, pageSize, query_columns=None):
        """
        分页查询列表
        :param offset: 分页
        :param pageSize: 分页数
        :param query_columns: 需要查询列
        :return:
        """
        limit = f" LIMIT {pageSize} OFFSET {offset} "
        return self.sqlite3.select_data_by_limit(self.table_name, columns=query_columns, limit=limit)
        # new_list_of_tuples = []
        # if sql_data is not None:
        #     for t in sql_data:
        #         ts = (t[1], t[2], t[4], t[5], [8], t[9], t[12], t[14], t[17], t[23], t[24], t[28])
        #         new_list_of_tuples.append(ts)
        # return new_list_of_tuples

    def insertCrawlerBindata(self, data: dict):
        """
        字典插入
        :param data:
        :return:
        """
        with self.sqlite3.conn:  # 添加事务
            self.sqlite3.insert_data(self.table_name, data)

    def insertCrawlerBinByTuple(self, data: tuple, headers=None):
        """
        元组插入
        :param headers:
        :param data:
        :return:
        """
        with self.sqlite3.conn:
            self.sqlite3.insert_data_tuple(self.table_name, data, headers)

    def batchInsertCrawlerBinByTuple(self, data, headers=None):
        """
        元组批量插入
        :param headers:
        :param data: 需要保存的元组数据
        :return:
        """
        with self.sqlite3.conn:
            self.sqlite3.batch_insert_data_by_tuple(self.table_name, data, headers)

    def selectInfoByCrawlerBin(self, bin_number, query_columns=None):
        """
        根据查询
        :return:
        """
        condition = f" bin_number = '{bin_number}' "
        return self.sqlite3.select_data(self.table_name, columns=query_columns, condition=condition)

    def selectListByCountry(self, country, query_columns=None):
        """
        根据国家查询列表
        :return:
        """
        condition = f" country = '{country}' "
        return self.sqlite3.select_data(self.table_name, columns=query_columns, condition=condition)

    def selectListByBanksAndCondition(self,
                                      banks,
                                      card,
                                      cardType,
                                      cardLevel, query_columns=None):
        """
        根据银行查询列表
        :return:
        """
        condition = ''
        banks = tuple(banks)
        if len(banks) > 0:
            if len(banks) == 1:
                condition = f" bank like '%{banks[0]}%' "
            else:
                condition = f" bank in {banks} "
        if card is not None:
            condition = f"{condition} and card = '{card}'"
        if cardType is not None:
            condition = f"{condition} and card_type = '{cardType}'"
        if cardLevel is not None:
            condition = f"{condition} and card_level = '{cardLevel}'"
        condition = condition.strip()
        if condition.startswith("and"):
            condition = condition[3:]
        print(condition)
        limit = f" LIMIT {1000} OFFSET {0} "
        return self.sqlite3.select_data_by_limit(self.table_name, columns=query_columns, condition=condition,
                                                 limit=limit)

    def selectCrawlerBinByCrawlerBins(self, bin_numbers,
                                      card=None, cardType=None,
                                      cardLevel=None, query_columns=None):
        """
        根据bins查询
        :return:
        """
        condition = ""
        bin_numbers = tuple(bin_numbers)
        if len(bin_numbers) > 0:
            if len(bin_numbers) == 1:
                condition = f" bin_number = '{bin_numbers[0]}' "
            else:
                condition = f" bin_number in {bin_numbers} "
        if card is not None:
            condition = f"{condition} and card = '{card}'"
        if cardType is not None:
            condition = f"{condition} and card_type = '{cardType}'"
        if cardLevel is not None:
            condition = f"{condition} and card_level = '{cardLevel}'"
        condition = condition.strip()
        if condition.startswith("and"):
            condition = condition[3:]
        print(condition)
        return self.sqlite3.select_data(self.table_name, columns=query_columns, condition=condition)

    def batchDelByCrawlerBins(self, bins):
        """
         批量删除BINS
        :param bins: bin的集合
        :return:
        """
        if bins is None or len(bins) == 0:
            return
        binlist = tuple(bins)
        if len(binlist) == 1:
            condition = f" bin_number = '{binlist[0]}' "
        else:
            condition = f" bin_number in {binlist} "
        with self.sqlite3.conn:  # 添加事务
            self.sqlite3.delete_data(table_name=self.table_name, condition=condition)

    def selectExportData(self, startTime, endTime):
        """
        导出数据
        :param startTime: 开始时间
        :param endTime: 结束时间
        :return:
        """
        conditon = None
        if startTime is not None and endTime is not None:
            conditon = f" update_time >= '{startTime}' and update_time <= '{endTime}' "
        return self.sqlite3.select_data_by_limit(self.table_name, condition=conditon)

    def updateRemark(self, BIN: str, remark: str):
        """
        修改 remark
        :param BIN:
        :param remark:
        :return:
        """
        with self.sqlite3.conn:  # 添加事务
            data = {
                'bin_number': f'{BIN}',
                'remark': f'{remark}'
            }
            self.sqlite3.update_data(self.table_name, data,
                                     condition="bin_number =:bin_number")
